package com.sample.javabean;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import com.sample.action.form.RoleForm;
import com.sample.jdbc.JdbcUtils;
import com.sample.util.Pager;

/**
 * 定义角色模型，封装角色属性及方法
 * @author 演示
 *
 */
public class Role {
	private Integer id;
	private String roleid;
	private String remark;
	private String menulistid;
	private String menulistname;
	private String rolename;
	
	// 实例化JdbcUtils组件，完成加载驱动以及连接数据库操作，获得连接实例
	private JdbcUtils jdbcUtils = new JdbcUtils();
	
	public Role(String roleid){
		this.roleid =roleid;
	}
	/**
	 * 根据角色编号加载角色信息
	 * @throws Exception
	 */
	public void load()throws Exception{
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null; 
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "select * from  t_role where id='"+this.id+"'";
			// 4:执行SQL并返回结果
			rs=stmt.executeQuery(sql);
			if(rs.next()){
				this.id = rs.getInt("id");
				this.roleid =rs.getString("roleid");
				this.remark =rs.getString("remark");
				this.menulistid =rs.getString("menulistid");
				this.rolename =rs.getString("rolename");
			}
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException("加载角色信息失败！");
		} finally {
			jdbcUtils.close(conn, stmt);
		}	
	}
	/**
	 * 添加角色
	 */
	public void addRole() {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null; 
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			String[] menulistid = this.menulistid.split(",");
			StringBuffer sb = new StringBuffer();
			for(int i = 0;i<menulistid.length;i++){
				String sql = "select menuname from  t_menu where id='"+menulistid[i]+"'";
				// 4:执行SQL并返回结果
				rs=stmt.executeQuery(sql);
				if(rs.next()){
					String menuname = rs.getString("menuname");
					sb.append(menuname).append(",");
				}
			}
			String menulistname = sb.substring(0,sb.length() - 1);
			// 3:构造插入SQL
			String sql = "insert into t_role(roleid,remark,rolename,menulistid,menulistname) values('"
					+ this.roleid + "','" + this.remark + "','" + this.rolename + "','" + this.menulistid + "','" + menulistname + "')";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	public void editRole() {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null; 
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			String[] menulistid = this.menulistid.split(",");
			StringBuffer sb = new StringBuffer();
			for(int i = 0;i<menulistid.length;i++){
				String sql = "select menuname from  t_menu where id='"+menulistid[i]+"'";
				// 4:执行SQL并返回结果
				rs=stmt.executeQuery(sql);
				if(rs.next()){
					String menuname = rs.getString("menuname");
					sb.append(menuname).append(",");
				}
			}
			String menulistname = sb.substring(0,sb.length() - 1);
			// 3:构造插入SQL
			String sql = "update  t_role set roleid='"+this.roleid+"',remark='"+this.remark+"',menulistid='"+this.menulistid+"',menulistname='"+menulistname+"',rolename='"+this.rolename+"' where id='"+this.id+"'";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	public void delRole() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "delete from t_role  where id='"+this.id+"'";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	/**
	 * 分页显示角色信息
	 * @return
	 */
	public Pager<Role> list(int page,int size,RoleForm roleForm,String keywords) {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null;
		Pager<Role> roles=new Pager<Role>();
		roles.setPage(page);
		roles.setSize(size);
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			int start=(page-1)*size;
			int limit=size;
			//查询SQL
			String selectSql="select * from t_role where 1=1";
			//统计SQL
			String countSql ="select count(*)as c from ("+selectSql+") as t1";
			//模糊查询
			if(null != keywords && "" != keywords && !"".equals(keywords)){
				selectSql = selectSql+"   and (roleid like '%"+keywords+"%' or rolename like '%"+keywords+"%')";
			}
			
			//分页SQL
			 String pageSql=selectSql+" limit "+start+","+limit;
			 rs= stmt.executeQuery(countSql);
			 int total=0;
			 while(rs.next()){
				 total=rs.getInt("c");
			 }
			 roles.setTotal(total);
			 //查询分页信息
			 rs= stmt.executeQuery(pageSql);
			 while(rs.next()){
			
				 int id=rs.getInt("id");
				  String roleid=rs.getString("roleid");
				  String remark=rs.getString("remark");
				  String rolename=rs.getString("rolename");
				  Role role=new Role(roleid, remark, rolename, id);
				  role.setMenulistid(rs.getString("menulistid"));
				  role.setMenulistname(rs.getString("menulistname"));
				  roles.getDataSets().add(role);
			 }
			 roles.init();
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}
		return roles;

	}
	
	/**
	 * 
	 * @return
	 */
	public List<Role> findAll(){
		Connection conn=null;
		Statement stmt =null;
		ResultSet rs =null;
		List<Role> roleList =new ArrayList<Role>(20);
		try{
			 conn =jdbcUtils.getConn();
			 stmt =conn.createStatement();
			String sql="select * from t_role where 1=1";
			 rs =stmt.executeQuery(sql);
			while(rs.next()){
				Role model=new Role();
				model.setRolename(rs.getString("rolename"));
				roleList.add(model);
				
			}
			
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			jdbcUtils.close(conn, stmt,rs);
		}
		return roleList;
		
	}
	
	//定义无参构造函数
	public Role(){
		
	}
	//定义带参数构造函数
	public Role(String roleid,String remark,String rolename,int id){
		this.roleid=roleid;
		this.remark =remark;
		this.rolename=rolename;
		this.id =id;
	}

	//定义访问成员属性方法
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getRoleid() {
		return roleid;
	}
	public void setRoleid(String roleid) {
		this.roleid = roleid;
	}
	public String getRemark() {
		return remark;
	}
	public void setRemark(String remark) {
		this.remark = remark;
	}
	public String getRolename() {
		return rolename;
	}
	public void setRolename(String rolename) {
		this.rolename = rolename;
	}
	public String getMenulistid() {
		return menulistid;
	}
	public void setMenulistid(String menulistid) {
		this.menulistid = menulistid;
	}
	public String getMenulistname() {
		return menulistname;
	}
	public void setMenulistname(String menulistname) {
		this.menulistname = menulistname;
	}

}
